#
# This file is a part of the open-eBackup project.
# This Source Code Form is subject to the terms of the Mozilla Public License, v. 2.0.
# If a copy of the MPL was not distributed with this file, You can obtain one at
# http://mozilla.org/MPL/2.0/.
#
# Copyright (c) [2024] Huawei Technologies Co.,Ltd.
#
# THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
# EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
# MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
#

import locale
import os
import platform
import signal

from common.common import read_result_file, touch_file
from common.file_common import delete_file
from oracle import logger
from oracle.common.common import chown_file_path_owner, exec_sql_cmd, parse_html_result, \
    login_oracle_database_with_params, login_oracle_database, parse_sql_result
from oracle.common.constants import PluginPathConstant, ScriptExitCode, RETURN_INVALID, PexpectResult, \
    DataBaseRole, OpenMode
from oracle.common.linux_common import execute_linux_sqlplus_cmd
from oracle.common.windows_common import write_tmp_file, execute_windows_sqlplus_cmd


def get_archive_dest_list(pid, params):
    encoding = locale.getdefaultlocale()[1]
    arch_dest_list = []

    logger.info("get_archive_dest_list start! pid = %s", pid)
    if platform.system().lower() == "windows":
        tmp_sql = f"{PluginPathConstant.WINDOWS_TMP_PATH}/archive_dest_sql_{pid}.sql"
        tmp_rst = f"{PluginPathConstant.WINDOWS_TMP_PATH}/archive_dest_sql_{pid}.txt"
    else:
        tmp_sql = f"{PluginPathConstant.STMP_PATH}/archive_dest_sql_{pid}.sql"
        tmp_rst = f"{PluginPathConstant.STMP_PATH}/archive_dest_sql_{pid}.txt"

    ret = get_valid_destination(pid, params, tmp_sql, tmp_rst)
    if ret != ScriptExitCode.SUCCESS:
        logger.error("Get Archive log dest list failed. pid = %s", pid)
        delete_file(tmp_sql)
        delete_file(tmp_rst)
        return ret, arch_dest_list
    tmp_line = read_result_file(tmp_rst, encoding=encoding).strip()
    delete_file(tmp_sql)
    delete_file(tmp_rst)
    for line in tmp_line.splitlines():
        if line == "USE_DB_RECOVERY_FILE_DEST":
            ret = get_recovery_file_dest_name(pid, params, tmp_sql, tmp_rst)
            if ret != ScriptExitCode.SUCCESS:
                logger.error("Get RECOVERY_FILE_DEST failed, ret = %d. pid = %s", ret, pid)
                delete_file(tmp_sql)
                delete_file(tmp_rst)
                return ret, arch_dest_list
            archived_dest = read_result_file(tmp_rst, encoding=encoding).strip()
            logger.info("archived_dest=%s", archived_dest)
            arch_dest_list.append(archived_dest)
            delete_file(tmp_sql)
            delete_file(tmp_rst)
        elif line:
            arch_dest_list.append(line.strip())
        else:
            logger.warning("get line is empty, continue")
    logger.info(f"Get Archive Dest List success,arch_dest_list={arch_dest_list}, pid: {pid}.")
    return ScriptExitCode.SUCCESS, arch_dest_list


def get_valid_destination(pid, params, tmp_sql, tmp_rst):
    instance_name = params.get("instance_name", "")
    content = f"set linesize 300;\n" \
              f"col DESTINATION for a255;\n" \
              f"SPOOL {tmp_rst}\n" \
              f"select DESTINATION from v$archive_dest where STATUS='VALID';\n" \
              f"SPOOL OFF\n" \
              f"exit;"
    write_tmp_file(tmp_sql, content)
    set_db_silence_sql(tmp_sql)
    if platform.system().lower() == "windows":
        ret = execute_windows_sqlplus_cmd(pid, params, tmp_sql, tmp_rst, instance_name)
    else:
        touch_file(tmp_rst)
        chown_file_path_owner(tmp_rst, params.get("db_install_user", ""))
        chown_file_path_owner(tmp_sql, params.get("db_install_user", ""))
        ret = execute_linux_sqlplus_cmd(pid, params, tmp_sql)
    return ret


def get_recovery_file_dest_name(pid, params, tmp_sql, tmp_rst):
    instance_name = params.get("instance_name", "")
    content = f"set linesize 300;\n" \
              f"col NAME for a255;\n" \
              f"SPOOL {tmp_rst}\n" \
              f"select NAME from v$RECOVERY_FILE_DEST;\n" \
              f"SPOOL OFF\n" \
              f"exit;"
    write_tmp_file(tmp_sql, content)
    set_db_silence_sql(tmp_sql)
    logger.info("Exec SQL to get name of archive dest. pid = %s", pid)
    if platform.system().lower() == "windows":
        ret = execute_windows_sqlplus_cmd(pid, params, tmp_sql, tmp_rst, instance_name)
    else:
        touch_file(tmp_rst)
        chown_file_path_owner(tmp_rst, params.get("db_install_user", ""))
        chown_file_path_owner(tmp_sql, params.get("db_install_user", ""))
        ret = execute_linux_sqlplus_cmd(pid, params, tmp_sql)
    return ret


def set_db_silence_sql(tmp_sql):
    if not tmp_sql or not os.path.isfile(tmp_sql):
        logger.error("Sql file is none or not exist! Set db silence failed!")
        return RETURN_INVALID

    content = f"set pagesize 0\n" \
              f"set feedback off\n" \
              f"set newpage none\n" \
              f"set heading off\n"

    encoding = locale.getdefaultlocale()[1]
    original_content = read_result_file(tmp_sql, encoding=encoding)
    content += original_content
    write_tmp_file(tmp_sql, content)
    return ScriptExitCode.SUCCESS


def get_first_scn_contains_time(pid, login_params, contains_time):
    # 从V$ARCHIVED_LOG表中查找包含了contains_time时间的归档日志文件的FIRST_CHANGE#
    first_scn = ''
    result, child = login_oracle_database_with_params(pid, login_params, timeout=30)
    if not result:
        return first_scn, 'login database failed.'
    cmd = f"SELECT TO_CHAR(FIRST_CHANGE#) AS FIRST_CHANGE# FROM V$ARCHIVED_LOG " \
          f"WHERE NAME IS NOT NULL AND NAME NOT LIKE '/mnt/databackup/%' " \
          f"and TO_DATE('{contains_time}', 'YYYY-MM-DD HH24:MI:SS')  BETWEEN FIRST_TIME and NEXT_TIME;"
    try:
        exec_status, std_out = exec_sql_cmd(child, cmd, PexpectResult.NO_ROWS_SELECTED)
    except Exception as exception:
        logger.error(f'exec cmd failed. {str(exception)}')
        return first_scn, 'get archivelog scn before the specified time failed.'
    finally:
        if platform.system().lower() == "windows":
            child.kill(signal.SIGTERM)
        else:
            child.close()
    logger.info(f"exec_status :{exec_status},std_out:{std_out}")
    if isinstance(std_out, bytes):
        std_out = std_out.decode(encoding="utf-8")
    if std_out.__contains__('no rows selected'):
        return first_scn, ''
    if exec_status:
        results = parse_html_result(std_out)
        first_scn = results[0].get('FIRST_CHANGE#').replace('\r', '').replace('\n', '')
        logger.info(f"get_time_from_scn :{first_scn}")
        return first_scn, ''
    logger.error(f"Failed to get_time_from_scn cmd: {cmd}, std_out: {std_out}.")
    return first_scn, 'get archivelog scn before the specified time failed.'


def get_last_archive_log_next_change(pid, login_params, end_scn):
    next_change = ''
    result, child = login_oracle_database_with_params(pid, login_params, timeout=30)
    if not result:
        return next_change, 'login database failed.'
    cmd = f"SELECT TO_CHAR(MIN(MAXCHANGE)) AS CHANGE FROM (SELECT MAX(NEXT_CHANGE#) MAXCHANGE FROM V$ARCHIVED_LOG " \
          f"WHERE NAME IS NOT NULL " \
          f"AND NAME NOT LIKE '/MNT/DATABACKUP/%' AND NEXT_CHANGE# <= {end_scn} GROUP BY THREAD#);"
    try:
        exec_status, std_out = exec_sql_cmd(child, cmd, PexpectResult.NO_ROWS_SELECTED)
    except Exception as exception:
        logger.error(f'exec cmd {cmd} failed. {str(exception)}')
        return next_change, 'get archivelog scn before the specified time failed.'
    finally:
        if platform.system().lower() == "windows":
            child.kill(signal.SIGTERM)
        else:
            child.close()
    if isinstance(std_out, bytes):
        std_out = std_out.decode(encoding="utf-8")
    if std_out.__contains__('no rows selected'):
        return next_change, ''
    if exec_status:
        results = parse_html_result(std_out)
        next_change = results[0].get('CHANGE').replace('\r', '').replace('\n', '')
        logger.info(f"get_last_archive_log_next_change :{next_change}")
        return next_change, ''
    logger.error(f"Failed to get_last_archive_log_next_change cmd: {cmd}, std_out: {std_out}.")
    return next_change, 'get archivelog scn before the specified time failed.'


def create_new_temp_table_space_sql(pid, login_params, new_temp_tbs_path, pdb_name, cmd=''):
    assist_ins_sid = login_params['instance_name']
    result, child = login_oracle_database_with_params(pid, login_params, timeout=30)
    if not result:
        logger.error(f"Failed to login assist ins, sid:{assist_ins_sid}")
    cmd += f"CREATE TEMPORARY TABLESPACE new_temp_tbs_{pdb_name} TEMPFILE " \
           f"'{new_temp_tbs_path}' SIZE 100M AUTOEXTEND ON;\n" \
           f"ALTER DATABASE DEFAULT TEMPORARY TABLESPACE new_temp_tbs_{pdb_name};"
    try:
        exec_sql_cmd(child, cmd, PexpectResult.ALTER_DATABASE)
    except Exception as exception:
        logger.error(f'exec cmd {cmd} failed. {str(exception)}')
    finally:
        if platform.system().lower() == "windows":
            child.kill(signal.SIGTERM)
        else:
            child.close()


def get_pdb_open_cluster_inst(pid, login_params):
    logger.info("Start querying the instance name of read write pdb status.")
    instance_name_pdb_open = ''
    result, child = login_oracle_database_with_params(pid, login_params, timeout=30)
    if not result:
        logger.error(f"Failed to login cluster ins, sid:{login_params['instance_name']}")
        return instance_name_pdb_open
    cmd = ("SELECT p.INST_ID, i.INSTANCE_NAME,i.STATUS,p.NAME,p.OPEN_MODE"
           " FROM GV$PDBS p"
           " JOIN GV$INSTANCE i ON p.INST_ID = i.INST_ID"
           " WHERE p.OPEN_MODE = 'READ WRITE' AND p.NAME <> 'PDB$SEED' AND i.STATUS = 'OPEN'"
           " ORDER BY p.INST_ID;")
    try:
        return_code, std_out = exec_sql_cmd(child, cmd, PexpectResult.NO_ROWS_SELECTED)
        if return_code:
            results = parse_html_result(std_out)
            if results:
                instance_name_pdb_open = results[0].get('INSTANCE_NAME', '')
        else:
            logger.error(f"Failed to execute cmd: {cmd}, std_out: {std_out}, pid: {pid}.")
    except Exception as exception:
        logger.error(f'exec cmd {cmd} failed. {exception}')
    finally:
        if platform.system().lower() == "windows":
            child.kill(signal.SIGTERM)
        else:
            child.close()
    return instance_name_pdb_open


def get_table_is_has_foreign_key_dict(pid, login_params, pdb_name):
    logger.info("Start querying foreign_key info.")
    is_has_foreign_key_dict = {}
    result, child = login_oracle_database_with_params(pid, login_params, timeout=30)
    if not result:
        logger.error(f"Failed to login cluster ins, sid:{login_params['instance_name']}")
        return is_has_foreign_key_dict
    cmd = ''
    if pdb_name:
        cmd = f"alter session set container={pdb_name};\n"
    cmd += "select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where CONSTRAINT_TYPE='R';"
    try:
        return_code, std_out = exec_sql_cmd(child, cmd, PexpectResult.NO_ROWS_SELECTED)
        if return_code and std_out:
            results = parse_html_result(std_out)
            is_has_foreign_key_dict = {
                item.get('TABLE_NAME', ''): item
                for item in results
                if item.get('TABLE_NAME', '')
            }
        else:
            logger.error(f"Failed to execute cmd: {cmd}, std_out: {std_out}, pid: {pid}.")
    except Exception as exception:
        logger.error(f'exec cmd {cmd} failed. {exception}')
    finally:
        if platform.system().lower() == "windows":
            child.kill(signal.SIGTERM)
        else:
            child.close()
    return is_has_foreign_key_dict


def check_database_is_dg(pid, db_instance_name, install_user_name):
    """
    查询当前数据库是否是dg
    :param pid: pid
    :param db_instance_name: 数据库实例名
    :param install_user_name: 数据库安装用户名
    :return: 是/否
    """
    logger.info(f"Execute check database is primary, pid: {pid}, database instance name: {db_instance_name}, "
                f"install user name: {install_user_name}.")

    cmd = f"select database_role,open_mode from v$database;"
    result, child = login_oracle_database(pid, db_instance_name, install_user_name)
    if not result:
        return False
    try:
        return_code, std_out = exec_sql_cmd(child, cmd)
        if return_code:
            database_role = parse_sql_result(std_out, "DATABASE_ROLE")
            open_mode = parse_sql_result(std_out, "OPEN_MODE")
            logger.info(f'database_role: {database_role}, open_mode:{open_mode}.')
            return database_role != DataBaseRole.PRIMARY and open_mode == OpenMode.MOUNTED.value
        logger.error(f"Failed to execute cmd: {cmd}, pid: {pid}.")
        return False
    finally:
        if platform.system().lower() == "windows":
            child.kill(signal.SIGTERM)
        else:
            child.close()


def check_database_block_change_tracking(pid, login_params):
    """
    查询当前数据库是否开启块跟踪
    :param pid: pid
    :param login_params: 数据库登录参数
    :return: 是/否
    """
    logger.info(f"Execute check database block_change_tracking is enable, pid: {pid}")
    cmd = f"SELECT STATUS FROM V$BLOCK_CHANGE_TRACKING;"
    result, child = login_oracle_database_with_params(pid, login_params)
    if not result:
        return False
    try:
        return_code, std_out = exec_sql_cmd(child, cmd)
        if return_code:
            status = parse_sql_result(std_out, "STATUS")
            logger.info(f'block_change_tracking status: {status}.')
            return status == "ENABLED"
        logger.error(f"Failed to execute cmd: {cmd}, pid: {pid}.")
        return False
    finally:
        if platform.system().lower() == "windows":
            child.kill(signal.SIGTERM)
        else:
            child.close()


def get_current_scn(pid, login_params):
    logger.info(f"Execute get current scn, pid: {pid}")
    cmd = f"SELECT TO_CHAR(CURRENT_SCN) AS CURRENT_SCN FROM V$DATABASE;"
    result, child = login_oracle_database_with_params(pid, login_params)
    if not result:
        return ""
    try:
        return_code, std_out = exec_sql_cmd(child, cmd)
        if return_code:
            current_scn = parse_sql_result(std_out, "CURRENT_SCN")
            logger.info(f'get current scn: {current_scn}.')
            return current_scn
        logger.error(f"Failed to execute cmd: {cmd}, pid: {pid}.")
        return ""
    finally:
        if platform.system().lower() == "windows":
            child.kill(signal.SIGTERM)
        else:
            child.close()


def list_temporary_objects(pid, pdb_name, login_params):
    """
    查询所有临时对象
    :param pid: pid
    :param pdb_name: pdb名称
    :param login_params: 数据库登录参数
    :return: 临时对象数组
    """
    logger.info(f"Execute check table is temporary, pid: {pid}")
    cmd = ""
    if pdb_name:
        cmd = f"alter session set container={pdb_name};\n"
    cmd += f"select object_name from ALL_OBJECTS where temporary = 'Y';"
    result, child = login_oracle_database_with_params(pid, login_params)
    if not result:
        return []
    try:
        return_code, std_out = exec_sql_cmd(child, cmd, PexpectResult.FILE_PATH)
        if return_code:
            if PexpectResult.FILE_PATH[3] in str(std_out):
                logger.warn(f"Failed to find temporary objects: {cmd}, pid: {pid}.")
                return []
            return parse_html_result(std_out)
        logger.warn(f"Failed to execute cmd: {cmd}, pid: {pid}, std_out:{std_out}.")
        return []
    finally:
        if platform.system().lower() == "windows":
            child.kill(signal.SIGTERM)
        else:
            child.close()


def get_local_listener(pid, login_params):
    """
    查询当前数据库local_listener
    :param pid: pid
    :param login_params: 数据库登录参数
    :return: 是/否
    """
    local_listener = ''
    result, child = login_oracle_database_with_params(pid, login_params, timeout=30)
    if not result:
        return local_listener
    cmd = 'show parameter local_listener;'
    try:
        exec_status, std_out = exec_sql_cmd(child, cmd)
    except Exception as exception:
        logger.error(f'exec cmd failed. {str(exception)}')
        return local_listener
    finally:
        if platform.system().lower() == "windows":
            child.kill(signal.SIGTERM)
        else:
            child.close()
    if exec_status:
        results = parse_html_result(std_out)
        local_listener = results[0].get('VALUE').replace('\r', '').replace('\n', '')
        logger.info(f"get_local_listener: {local_listener}")
        return local_listener
    logger.error(f"Failed to get_local_listener cmd: {cmd}, std_out: {std_out}.")
    return local_listener
